﻿-- FUNCTION: public.contractshenqian(character varying, integer, integer, integer)

-- DROP FUNCTION IF EXISTS public.contractshenqian(character varying, integer, integer, integer);

CREATE OR REPLACE FUNCTION public.contractshenqian(
	p_bill character varying,
	p_billid integer,
	p_typeid integer,
	p_org_id integer)
    RETURNS json
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
declare
    v_cnt integer;
    v_orgunitname character varying(200);
    v_data json;  
    v_iskeyan integer;
    v_orgname  character varying(200);
	v_workflow_bill_stateid integer;
begin  
-----current_date
   select max(id) into v_workflow_bill_stateid From workflow_bill_state where bill_enum=p_bill and billid=p_billid;
   if p_typeid=1 then
   -----------创建缓存表
	select count(*) into v_cnt from information_schema.tables where table_name='temp_tab';
        if v_cnt>0 then
           drop table temp_tab;
        end if;
        v_iskeyan :=0;
        
        if p_bill='contracts' then
	    select  count(*) into v_cnt From  public.contracts where ('党团资金' = ANY(amounttype) and id=p_billid
             and departmentid in (select id From public.orgunit where name in('党委办公室')))or
              ('工会资金' = ANY(amounttype) and id=p_billid
             and departmentid in (select id From public.orgunit where name in('工会')));

             select iskeyan into v_iskeyan From contracts where id=p_billid;
        else
          v_cnt :=0;
        end if; 
        
	CREATE TEMP TABLE temp_tab as (
	  select 1 as id,id as stateid,name || '意见' as statename,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion  
	  From public.workflow_state where name =case when v_iskeyan=0 then '业务部门' else '科教部门' end
	 union
	 select 2 as id,id as stateid,name || '意见' as statename,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion  
	 From public.workflow_state where name ='法律顾问'
	 union
	 select 3 as id,id as stateid,name || '意见' as statename,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion
	   From public.workflow_state where name ='采购部门'
	 union
	 select 4 as id,id as stateid,name || '意见' as statename,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion
	   From public.workflow_state where name ='财务部门'
	 union
	 select 5 as id,id as stateid,name || '意见' as statename,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion
	   From public.workflow_state where name ='审计部门'
	 union
	 select 6 as id,id as stateid,name || '意见' as statename ,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion
	  From public.workflow_state where name ='业务部门分管领导'
	 union
	 select 7 as id,id as stateid,name || '意见' as statename ,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion
	  From public.workflow_state where name ='采购部门分管领导'
	 union
	 select 8 as id,id as stateid,name || '意见' as statename ,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion
	  From public.workflow_state where name ='总会计师'
	 union
	 select 9 as id,id as stateid,name || '意见' as statename ,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion
	  From public.workflow_state where name =case when v_cnt>0 then '书记审批' else '院长审批' end
	  );
	  if p_bill='contracts' then
	     select t1.name into v_orgunitname
	     from public.orgunit t1 inner join public.contracts t2 on t1.id=t2.departmentid and t2.id=p_billid;
	     if v_orgunitname='审计部'  then
		update temp_tab set stateid=(select stateid from temp_tab where id=1) where id=5;
	     elsif v_orgunitname='财务部' then
		update temp_tab set stateid=(select stateid from temp_tab where id=1) where id=4;
		select count(*) into v_cnt From public.contracts where id=p_billid and amount>=10000;
		     if v_cnt>0 then
			update temp_tab set stateid=(select stateid from temp_tab where id=6) where id=8;
		     end if;
	     elsif v_orgunitname='医疗保险部' then
		select count(*) into v_cnt From public.contracts where id=p_billid and amount>1000;
		     if v_cnt>0 then
			update temp_tab set stateid=(select stateid from temp_tab where id=6) where id=8;
		     end if;
	     elsif v_orgunitname='采购保障部' then
		update temp_tab set stateid=(select stateid from temp_tab where id=1) where id=3;
		select count(*) into v_cnt From public.contracts where id=p_billid and amount>=10000;
		     if v_cnt>0 then
			update temp_tab set stateid=(select stateid from temp_tab where id=6) where id=7;
		     end if;
	     elsif v_orgunitname='事业发展部' then
		select count(*) into v_cnt From public.contracts where id=p_billid and amount>=10000;
		     if v_cnt>0 then
			update temp_tab set stateid=(select stateid from temp_tab where id=6) where id=7;
		     end if;
	     elsif v_orgunitname='信息部' or v_orgunitname='网络医院办公室' then
		select count(*) into v_cnt From public.contracts where id=p_billid and amount>=10000;
		     if v_cnt>0 then
			update temp_tab set stateid=(select stateid from temp_tab where id=6) where id=7;
		     end if;
	     elsif v_orgunitname='院务部' then
		     select count(*) into v_cnt From public.contracts where id=p_billid;
		     if v_cnt>0 then
			update temp_tab set stateid=(select stateid from temp_tab where id=6) where id=9;
		     end if;
	     elsif v_orgunitname='GCP机构办公室' then
		     update temp_tab set stateid=(select stateid from temp_tab where id=6) where id=9;
	     end if;
	  end if;
	  select max(name) into v_orgname from public.organizations;
	  if v_orgname !='成都市第六人民医院' then
		delete from temp_tab;
		insert into temp_tab(id,stateid,statename,username,comments,updated_at,annex,opinion)
		  select 1 as id,id as stateid,name || '意见' as statename,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion  
		  From public.workflow_state where name ='承办部门'
		 union
		 select 2 as id,id as stateid,name || '意见' as statename,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion  
		 From public.workflow_state where name ='医疗发展部'
		 union
		 select 3 as id,id as stateid,name || '意见' as statename,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion
		   From public.workflow_state where name ='财务部'
		 union
		 select 4 as id,id as stateid,name || '意见' as statename,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion
		   From public.workflow_state where name ='法律顾问'
		 union
		 select 5 as id,id as stateid,name || '意见' as statename,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion
		   From public.workflow_state where name ='分管院领导'
		 union
		 select 6 as id,id as stateid,name || '意见' as statename ,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion
		  From public.workflow_state where name ='法定代表人';
	  end if;
	  
	  update temp_tab set username=t2.name, comments=t1.comments,updated_at=to_char(t1.updated_at, 'YYYY年MM月DD日'),annex=t2.annex,
	  opinion=case when strpos(t1.comments,'原文：')>0 then '同意' when length(replace(t1.comments,'<p></p>',''))<2 then '同意' 
	  when length(replace(replace(replace(t1.comments,'/',''),'<code>',''),'<pre>',''))<2 then '同意'  else t1.comments end
	  from public.workflow_action t1 , public.users t2
	   where t1.workflow_bill_stateid = v_workflow_bill_stateid
            and t1.completed_by =t2.id and t1.workflow_stateid=temp_tab.stateid
             and t1.bill_enum=p_bill and t1.billid=p_billid ;
--------------------------------------------------------------------------------------------------
         if p_bill='contracts' then
	     select count(*) into v_cnt from temp_tab where id=1 and username='';
	     if v_cnt>0 then
		  update temp_tab set username=t2.name, comments='',updated_at=to_char(t1.bill_date, 'YYYY年MM月DD日'),annex='',
		  opinion='同意'
		  from public.contracts t1 , public.users t2
		   where t1.userid=t2.id and t1.id=p_billid and temp_tab.id=1;
	     end if;
	 end if;
--------------------------------------------------------------------------------------------------
           update temp_tab set updated_at=to_char(t1.updated_at, 'YYYY年MM月DD日'),opinion='详见附件法律顾问意见书'  
           from attaches t1 where  strpos(t1.annex::text,'lawyer')>0 and sourcetype=p_bill and sourceid=p_billid
           and temp_tab.id=2;
           update temp_tab set username='/' where length(username)<2;

          select array_to_json(array_agg(row_to_json(t)))
          into v_data   
          from (select *From temp_tab order by id)t;
   elsif p_typeid=2 then
   -----------创建缓存表
	select count(*) into v_cnt from information_schema.tables where table_name='temp_tab';
        if v_cnt>0 then
           drop table temp_tab;
        end if;
	CREATE TEMP TABLE temp_tab as (
	  select 1 as id,id as stateid,'基建项目部意见' as statename,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion  
	  From public.workflow_state where name ='业务部门'
	 union
	 select 2 as id,id as stateid,name || '意见' as statename,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion  
	 From public.workflow_state where name ='法律顾问'
	 union
	 select 4 as id,id as stateid,name || '意见' as statename,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion
	   From public.workflow_state where name ='财务部门'
	 union
	 select 5 as id,id as stateid,name || '意见' as statename,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion
	   From public.workflow_state where name ='审计部门'
	 union
	 select 6 as id,id as stateid,name || '意见' as statename ,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion
	  From public.workflow_state where name ='业务部门分管领导'
	 union
	 select 8 as id,id as stateid,name || '意见' as statename ,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion
	  From public.workflow_state where name ='总会计师'
	 union
	 select 9 as id,id as stateid,name || '意见' as statename ,'' as username,'' as comments,'' as updated_at,'' as annex,'' as opinion
	  From public.workflow_state where name ='院长审批'
	  );
	  if p_bill='contracts' then
	     select t1.name into v_orgunitname
	     from public.orgunit t1 inner join public.contracts t2 on t1.id=t2.departmentid and t2.id=p_billid;
	     if v_orgunitname='审计部'  then
		update temp_tab set stateid=(select stateid from temp_tab where id=1) where id=5;
	     elsif v_orgunitname='财务部' then
		update temp_tab set stateid=(select stateid from temp_tab where id=1) where id=4;
	     elsif v_orgunitname='采购保障部' then
		update temp_tab set stateid=(select stateid from temp_tab where id=1) where id=3;
		select count(*) into v_cnt From public.contracts where id=p_billid and amount>=10000;
		     if v_cnt>0 then
			update temp_tab set stateid=(select stateid from temp_tab where id=6) where id=7;
		     end if;
	     elsif v_orgunitname='院务部' then
		update temp_tab set stateid=(select stateid from temp_tab where id=6) where id=9;
	     end if;
	  end if;
	  update temp_tab set username=t2.name, comments=t1.comments,updated_at=to_char(t1.updated_at, 'YYYY年MM月DD日'),annex=t2.annex,
	  opinion=case when strpos(t1.comments,'原文：')>0 then '同意' when length(replace(t1.comments,'<p></p>',''))<2 then '同意' 
	  when length(replace(replace(replace(t1.comments,'/',''),'<code>',''),'<pre>',''))<2 then '同意' else t1.comments end
	  from public.workflow_action t1 , public.users t2
	   where t1.workflow_bill_stateid = v_workflow_bill_stateid
            and t1.completed_by =t2.id and t1.workflow_stateid=temp_tab.stateid
             and t1.bill_enum=p_bill and t1.billid=p_billid ;
             
           update temp_tab set updated_at=to_char(t1.updated_at, 'YYYY年MM月DD日'),opinion='详见附件法律顾问意见书'  
           from attaches t1 where  strpos(t1.annex::text,'lawyer')>0 and sourcetype=p_bill and sourceid=p_billid
           and temp_tab.id=2;
           update temp_tab set username='/' where length(username)<2;
           
          select array_to_json(array_agg(row_to_json(t)))
          into v_data   
          from (select *From temp_tab order by id)t;
   end if;
   return v_data;
END;  
$BODY$;

ALTER FUNCTION public.contractshenqian(character varying, integer, integer, integer)
    OWNER TO root;
